/**
 * Author Blaj Vlad
 * 14.03.2014
 */
package ro.msg.quiz.Model;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import ro.msg.quiz.Connection.ConnectJdbc;
import ro.msg.quiz.repository.MultipleChoiceQ;
import ro.msg.quiz.repository.Question;
import ro.msg.quiz.repository.Quiz;
import ro.msg.quiz.repository.RepositoryQuestion;
import ro.msg.quiz.repository.RepositoryQuiz;
import ro.msg.quiz.repository.SingleChoiceQ;
import ro.msg.quiz.repository.TextQ;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;

public class QuizJdbcModel {
	private Connection connection = null;
	private PreparedStatement preparedStatement = null;

	/**
	 * Insert a record into the database based on an object of type Quiz
	 * 
	 * @param quiz
	 */
	public void insertQuiz(Quiz quiz) {

		try {
			connection = (Connection) ConnectJdbc.getConnection();
			preparedStatement = (PreparedStatement) connection
					.prepareStatement("INSERT INTO quizzes (id,name)"
							+ "VALUES (?,?)");
			preparedStatement.setInt(1, quiz.getId());
			preparedStatement.setString(2, quiz.getQuizName());
			preparedStatement.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * Returns specific record (single person object) from table depending on
	 * "id" you provide
	 * 
	 * @param id
	 * @return
	 */
	public Quiz selectQuizById(int id) {
		Quiz quiz = new Quiz();

		ResultSet resultSet = null;

		try {
			connection = (Connection) ConnectJdbc.getConnection();
			preparedStatement = (PreparedStatement) connection
					.prepareStatement("SELECT * FROM quizzes WHERE id = ?");
			preparedStatement.setInt(1, id);
			resultSet = (ResultSet) preparedStatement.executeQuery();

			while (resultSet.next()) {
				quiz.setId(resultSet.getInt("id"));
				quiz.setQuizName(resultSet.getString("name"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return quiz;
	}

	/**
	 * Returns all records from table as a List of quiz objects.
	 * 
	 * @return list for all quizzes
	 */
	public ArrayList<Quiz> selectAllQuizzes() {
		ArrayList<Quiz> quizList = new ArrayList<Quiz>();
		Statement statement = null;
		ResultSet resultSet = null;

		try {
			connection = (Connection) ConnectJdbc.getConnection();
			statement = (Statement) connection.createStatement();
			resultSet = (ResultSet) statement
					.executeQuery("SELECT * FROM quizzes");

			while (resultSet.next()) {
				Quiz quiz = new Quiz();
				quiz.setId(resultSet.getInt("id"));
				quiz.setQuizName(resultSet.getString("name"));

				quizList.add(quiz);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return quizList;
	}

	/**
	 * Delete a record from the database in the table Quizzes.The search is made
	 * by the id
	 * 
	 * @param id
	 */
	public void deleteQuiz(int id) {

		try {
			// first we unbind all the relationships and then we delete the quiz
			connection = (Connection) ConnectJdbc.getConnection();
			preparedStatement = (PreparedStatement) connection
					.prepareStatement("DELETE FROM quizquestions WHERE idQuiz = ?");
			preparedStatement.setInt(1, id);
			preparedStatement.executeUpdate();
			preparedStatement = (PreparedStatement) connection
					.prepareStatement("DELETE FROM quizzes WHERE id = ?");
			preparedStatement.setInt(1, id);
			preparedStatement.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * Updates a specific record depending on "id" after you pass new Quiz
	 * object with new values
	 * 
	 * @param quiz
	 * @param id
	 */
	public void updateQuiz(Quiz quiz, int id) {

		try {
			connection = (Connection) ConnectJdbc.getConnection();
			preparedStatement = (PreparedStatement) connection
					.prepareStatement("UPDATE quizzes SET "
							+ " id = ?,name = ? WHERE id = ?");
			preparedStatement.setInt(1, id);
			preparedStatement.setString(2, quiz.getQuizName());
			preparedStatement.setInt(3, id);
			preparedStatement.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * Inserts a question in the database creates a record in the questions
	 * table creates records in the answers table
	 * 
	 * @param q
	 */
	public void insertQuestion(Question q) {
		ArrayList<String> options;
		ArrayList<Integer> correctOptions;
		ResultSet resultSet = null;
		int correctOption;
		try {
			connection = (Connection) ConnectJdbc.getConnection();
			preparedStatement = (PreparedStatement) connection
					.prepareStatement("INSERT INTO questions (id,text,type)"
							+ "VALUES (?,?,?)");
			preparedStatement.setInt(1, q.getId());
			preparedStatement.setString(2, q.getText());
			preparedStatement.setInt(3, q.getType());
			preparedStatement.executeUpdate();
			/*
			 * ResultSet generatedKeys = (ResultSet) preparedStatement
			 * .getGeneratedKeys(); if (null != generatedKeys &&
			 * generatedKeys.next()) { lastId = generatedKeys.getInt(1); }
			 */
			switch (q.getType()) {
			// 1 is singlechoice question
			case 1:
				SingleChoiceQ sq = (SingleChoiceQ) q;
				options = sq.getOptions();
				correctOption = sq.getCorrectOption();
				// nr is the counter to see which is the correct answer
				int nr = 1;
				for (String s : options) {
					preparedStatement = (PreparedStatement) connection
							.prepareStatement("INSERT INTO answers (idQuestion,text,correctAnswer)"
									+ "VALUES (?,?,?)");
					preparedStatement.setInt(1, sq.getId());
					preparedStatement.setString(2, s);
					if (nr == correctOption)
						preparedStatement.setInt(3, 1);
					else
						preparedStatement.setInt(3, 0);
					preparedStatement.executeUpdate();
					nr++;
				}
				break;
			// 2 is multiple choice question
			case 2:
				MultipleChoiceQ mq = (MultipleChoiceQ) q;
				options = mq.getOptions();
				correctOptions = mq.getCorrectOptions();
				// nr is the counter to see which is the correct answer
				nr = 1;

				// here we get the id of the last inserted index in order to
				// bind
				// the id of the answer to the id of the question

				for (String s : options) {
					preparedStatement = (PreparedStatement) connection
							.prepareStatement("INSERT INTO answers (idQuestion,text,correctAnswer)"
									+ "VALUES (?,?,?)");
					preparedStatement.setInt(1, mq.getId());
					preparedStatement.setString(2, s);
					if (correctOptions.contains(nr))
						preparedStatement.setInt(3, 1);
					else
						preparedStatement.setInt(3, 0);
					preparedStatement.executeUpdate();
					nr++;
				}
				break;
			// 3 is text question
			case 3:
				TextQ tq = (TextQ) q;
				// nr is the counter to see which is the correct answer
				preparedStatement = (PreparedStatement) connection
						.prepareStatement("INSERT INTO answers (idQuestion,text,correctAnswer)"
								+ "VALUES (?,?,?)");
				preparedStatement.setInt(1, tq.getId());
				preparedStatement.setString(2, tq.getAnswer());

				preparedStatement.setInt(3, 1);

				preparedStatement.executeUpdate();

				break;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * Returns a list of answers of a question.The search is id based
	 * 
	 * @param id
	 * @return
	 */
	public ArrayList<String> getAnswersById(int id) {
		ArrayList<String> answer_list = new ArrayList<String>();
		Statement statement = null;
		ResultSet resultSet = null;

		try {
			connection = (Connection) ConnectJdbc.getConnection();
			preparedStatement = (PreparedStatement) connection
					.prepareStatement("SELECT * FROM answers WHERE idQuestion = ?");
			preparedStatement.setInt(1, id);
			resultSet = (ResultSet) preparedStatement.executeQuery();
			while (resultSet.next()) {

				answer_list.add(resultSet.getString("text"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return answer_list;
	}

	/**
	 * Return an arraylist of integers, correct options from the database
	 * 
	 * @param id
	 * @return
	 */
	public ArrayList<Integer> getCorrectOptionsById(int id) {
		ArrayList<Integer> correct_option_list = new ArrayList<Integer>();
		Statement statement = null;
		ResultSet resultSet = null;
		// nr is the number of the row we count them and add in the option_list
		int nr = 1;
		int correctoption;
		try {
			connection = (Connection) ConnectJdbc.getConnection();
			preparedStatement = (PreparedStatement) connection
					.prepareStatement("SELECT * FROM answers WHERE idQuestion = ?");
			preparedStatement.setInt(1, id);
			resultSet = (ResultSet) preparedStatement.executeQuery();
			while (resultSet.next()) {
				correctoption = resultSet.getInt("correctAnswer");
				if (correctoption == 1)
					correct_option_list.add(nr);
				nr++;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return correct_option_list;
	}

	/**
	 * Returns an array list of Questions based on the record in the jdbc
	 */
	public ArrayList<Question> selectAllQuestions() {
		ArrayList<Question> question_list = new ArrayList<Question>();
		ArrayList<Integer> correct_option_list = new ArrayList<Integer>();
		ArrayList<String> answer_list = new ArrayList<String>();
		int correctoption;
		String Answer;
		String question_text;
		Statement statement = null;
		ResultSet resultSet = null;
		Statement statement_answer = null;
		ResultSet resultSet_answer = null;
		int id;
		Question question = null;
		try {
			connection = (Connection) ConnectJdbc.getConnection();
			statement = (Statement) connection.createStatement();
			resultSet = (ResultSet) statement
					.executeQuery("SELECT * FROM questions");

			while (resultSet.next()) {

				// based on the type of the question we instantiate a class of
				// the question
				int type = resultSet.getInt("type");
				switch (type) {
				// 1 is singlechoice question
				case 1:

					id = resultSet.getInt("id");
					question_text = resultSet.getString("text");
					answer_list = this.getAnswersById(id);
					correct_option_list = this.getCorrectOptionsById(id);
					// we get the index 0 because it is only one option
					correctoption = correct_option_list.get(0);
					question = new SingleChoiceQ(id, question_text,
							answer_list, correctoption);
					break;
				// 2 is multiple choice question
				case 2:

					id = resultSet.getInt("id");
					question_text = resultSet.getString("text");
					answer_list = this.getAnswersById(id);
					correct_option_list = this.getCorrectOptionsById(id);
					question = new MultipleChoiceQ(id, question_text,
							answer_list.size(), answer_list,
							correct_option_list);
					break;
				// 3 is text question
				case 3:
					id = resultSet.getInt("id");

					question_text = resultSet.getString("text");
					answer_list = this.getAnswersById(id);
					// we use the index 0 because for the text question we have
					// only one answer

					question = new TextQ(id, question_text, answer_list.get(0));
					break;
				}
				question_list.add(question);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return question_list;
	}

	/**
	 * Deletes a question from the database based on the id
	 * 
	 * @param id
	 */
	public void deleteQuestion(int id) {

		try {
			// first we unbind all the relationships and then we delete the quiz
			connection = (Connection) ConnectJdbc.getConnection();
			preparedStatement = (PreparedStatement) connection
					.prepareStatement("DELETE FROM quizquestions WHERE idQuestion = ?");
			preparedStatement.setInt(1, id);
			preparedStatement.executeUpdate();
			preparedStatement = (PreparedStatement) connection
					.prepareStatement("DELETE FROM answers WHERE idQuestion = ?");
			preparedStatement.setInt(1, id);
			preparedStatement.executeUpdate();
			preparedStatement = (PreparedStatement) connection
					.prepareStatement("DELETE FROM questions WHERE idQuestion = ?");
			preparedStatement.setInt(1, id);
			preparedStatement.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * Add a question to a quiz in the many to many relationship table This
	 * tables stores only ids
	 * 
	 * @param QuizId
	 * @param QuestionId
	 */
	public void addQuestionToQuiz(int QuizId, int QuestionId) {

		try {
			// first we unbind all the relationships and then we delete the quiz
			connection = (Connection) ConnectJdbc.getConnection();
			preparedStatement = (PreparedStatement) connection
					.prepareStatement("INSERT INTO quizquestions (idQuiz,idQuestion)"
							+ "VALUES (?,?)");
			preparedStatement.setInt(1, QuizId);
			preparedStatement.setInt(2, QuestionId);
			preparedStatement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * Update a question based on the id it first delete the question with the
	 * repesctive id and then replaces it with the Question question
	 * 
	 * @param question
	 * @param id
	 */
	public void updateQuestion(Question question, int id) {

		this.deleteQuestion(id);
		question.setId(id);
		this.insertQuestion(question);
	}

	/**
	 * Return the relation table from the database (the many to many table)
	 * 
	 * @return Map<Integer, ArrayList<Integer>>
	 */
	public Map<Integer, ArrayList<Integer>> getQuizQuestions() {
		Map<Integer, ArrayList<Integer>> QuizQuestions = new HashMap<Integer, ArrayList<Integer>>();
		ArrayList<Quiz> quizList = new ArrayList<Quiz>();
		Statement statement = null;
		ResultSet resultSet = null;
		int quizId = 0;
		int questionId = 0;
		try {
			connection = (Connection) ConnectJdbc.getConnection();
			statement = (Statement) connection.createStatement();
			resultSet = (ResultSet) statement
					.executeQuery("SELECT * FROM quizquestions");

			while (resultSet.next()) {
				quizId = resultSet.getInt("idQuiz");
				questionId = resultSet.getInt("idQuestion");
				if (QuizQuestions.containsKey(quizId)) {
					QuizQuestions.get(quizId).add(questionId);
				} else {
					ArrayList<Integer> emptyArray = new ArrayList<Integer>();
					QuizQuestions.put(quizId, emptyArray);
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return QuizQuestions;
	}

	/**
	 * Create an instance of the class IQuizModel which hold all the information
	 * in the database
	 * 
	 * @return IQuizModel
	 */
	public IQuizModel ImportSqlDatabase() {
		ArrayList<Question> questionsList = this.selectAllQuestions();
		ArrayList<Quiz> quizList = this.selectAllQuizzes();
		RepositoryQuestion repoQuestion = new RepositoryQuestion(questionsList);
		RepositoryQuiz repoQuiz = new RepositoryQuiz(quizList);
		IQuizModel model = new QuizModel(repoQuestion, repoQuiz);
		model.setQuizQuestions(this.getQuizQuestions());
		return model;
	}

	/**
	 * Export data into the sql database
	 * 
	 * @param model
	 *            of type IQuizModel
	 */
	public void ExportSqlDatabase(IQuizModel model) {
		Statement statement = null;
		try {
			connection = (Connection) ConnectJdbc.getConnection();
			statement = (Statement) connection.createStatement();
			statement.executeUpdate("delete FROM answers");
			statement.executeUpdate("delete FROM quizquestions");
			statement.executeUpdate("delete FROM quizzes");
			statement.executeUpdate("delete FROM questions");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		for (Quiz temp : model.getQuizRepo().getQuizes()) {
			this.insertQuiz(temp);
		}
		for (Question temp : model.getQuestionRepo().getQuestionsList()) {
			this.insertQuestion(temp);
		}
		for (Integer QuizId : model.getQuizQuestions().keySet()) {
			for (Integer QuestionId : model.getQuizQuestions().get(QuizId)) {
				this.addQuestionToQuiz(QuizId, QuestionId);
			}
		}
	}
}
